package com.vepick.metier.requetes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import com.vepick.beans.Reservation;

/*
 * M�thodes g�r�es par la station et communes aux clients abo ou ticket
 */
public class Requetes_reservation {

	/*
	 * Liste toutes les stations de la bdd, et pour chacune donne son adresse, ses velos dispo et ses bornes dispo
	 */
	public static void afficherLesReservations(Connection conn, int idClient) throws SQLException{
		//on cr�� une liste d'objet Station(idReserv, idStation, date de reservation et date de location souhaitee) pour stocker les donn�es
		List<Reservation> lesReservations = new ArrayList<>();
		//on recupere toutes les reservations de la bdd pour le client
		String requete = "SELECT idReserv, idStation, dateReserv, dateDebutLoc FROM RESERVATION WHERE idClient="+idClient+" ORDER BY dateDebutLoc ASC ";
		Statement stmt = conn.createStatement();
		ResultSet res = stmt.executeQuery(requete);
		while (res.next()) {
			lesReservations.add(new Reservation(res.getInt("idReserv"), res.getInt("idStation"), res.getString("dateReserv"), res.getString("dateDebutLoc")));
		}
		res.close();
		//et on affiche...!
		for (int j=0;j<lesReservations.size();j++){
			System.out.println("R�servation n� "+lesReservations.get(j).getIdReserv()+", � la station " +lesReservations.get(j).getIdStation()+""
					+ ": date de r�servation "+lesReservations.get(j).getDateReserv()+" pour le "+lesReservations.get(j).getDateDebutLoc()+". ");
		}
		System.out.println("\n");
	}
	
	/*
	 * Comme son nom l'indique, ajoute une reservation � un client...
	 */
	public static void ajouterReservation (Connection conn, int idClient) throws SQLException{
		//On r�cup�re les infos
		@SuppressWarnings("resource")
		Scanner sc = new Scanner(System.in);
		int idStation;
		String dateDebutLoc = null;
		String date= null;
		String heure = null;
		System.out.println("Saisissez le num�ro de station de depart pour la location");
		idStation = sc.nextInt();
		System.out.println("Saisissez la date de location souhaitee (JJ/MM/AAAA)");
		date = sc.next();
		System.out.println("Saisissez l'heure de location souhaitee (HH:MM)");
		heure= sc.next();
		dateDebutLoc = date +" "+heure;
		System.out.println(dateDebutLoc);
		//Requete d'ajout de la r�servation
		String query = "INSERT INTO RESERVATION VALUES (idReservation_seq.nextVal, "+idStation+" ,"+idClient+" ,sysdate, TO_DATE('"+dateDebutLoc+":00', 'dd/mm/yyyy hh24:mi:ss'))";
		Statement stmt = conn.createStatement();
		stmt.executeUpdate(query);
		conn.commit();
		System.out.println("La reservation a bien ete creee");
	}
	
	/*
	 * ...ou on en supprime une!
	 */
	public static void supprimerReservation (Connection conn, int idClient) throws SQLException{
		@SuppressWarnings("resource")
		Scanner sc = new Scanner(System.in);
		int idReserv;
		int testClient = -1;
		System.out.println("Saisissez le numero de reservation a supprimer");
		idReserv = sc.nextInt();
		//On v�rifie que l'abonn� supprime bien une de ses r�servations
		String queryVerifClient = "SELECT idClient FROM RESERVATION WHERE idReserv="+idReserv;
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(queryVerifClient);
		while (rs.next()){
			testClient = rs.getInt("idClient");
		}
		if (idClient != testClient){
			System.out.println("Erreur de saisie du numero de reservation");
		} else {
			//on supprime la r�servation
			String querySuppReserv = "DELETE FROM RESERVATION WHERE idReserv="+idReserv;
			stmt.executeUpdate(querySuppReserv);
			conn.commit();
			System.out.println("La r�servation n� "+ idReserv+" a bien �t� supprim�e");
		}
	}
	
	/*
	 * En bonus, on peut modifier la station de d�part...
	 */
	public static void modifierStationReserv (Connection conn, int idClient) throws SQLException{
		@SuppressWarnings("resource")
		Scanner sc = new Scanner(System.in);
		int idReserv = -1;
		int idStation = -1;
		int testClient = -1;
		System.out.println("Saisissez le numero de reservation a modifier");
		idReserv = sc.nextInt();
		//On v�rifie que l'abonn� supprime bien une de ses r�servations
		String queryVerifClient = "SELECT idClient FROM RESERVATION WHERE idReserv="+idReserv;
		Statement stmt = conn.createStatement();
		ResultSet rs = stmt.executeQuery(queryVerifClient);
		while (rs.next()){
			testClient = rs.getInt("idClient");
		}
		if (idClient != testClient){
			System.out.println("Erreur de saisie du numero de reservation a modifier");
		} else {
			//on recup�re la nouvelle station
			System.out.println("Saisissez la nouvelle station pour la reservation");
			idStation = sc.nextInt();
			//on met � jour la reservation avec la nouvelle station
			String queryModifStation = "UPDATE RESERVATION SET idStation="+idStation+" WHERE idReserv="+idReserv;
			stmt.executeUpdate(queryModifStation);
			conn.commit();
			System.out.println("La reservation n "+ idReserv+" a bien ete modifiee");
		}
	}
	
		/*
		 * ...et l'heure de location souhait�!!!
		 */
		public static void modifierDebutLocReserv (Connection conn, int idClient) throws SQLException{
			@SuppressWarnings("resource")
			Scanner sc = new Scanner(System.in);
			int idReserv = -1;
			String dateDebutLoc = null;
			String date= null;
			String heure = null;
			int testClient = -1;
			System.out.println("Saisissez le numero de reservation a modifier");
			idReserv = sc.nextInt();
			//On v�rifie que l'abonn� supprime bien une de ses r�servations
			String queryVerifClient = "SELECT idClient FROM RESERVATION WHERE idReserv="+idReserv;
			Statement stmt = conn.createStatement();
			ResultSet rs = stmt.executeQuery(queryVerifClient);
			while (rs.next()){
				testClient = rs.getInt("idClient");
			}
			if (idClient != testClient){
				System.out.println("Erreur de saisie du numero de reservation a modifier");
			} else {
				//on recup�re la nouvelle station
				System.out.println("Saisissez la nouvelle date de location souhaitee (JJ/MM/AAAA)");
				date = sc.next();
				System.out.println("Saisissez la nouvelle heure de location souhaitee (HH:MM)");
				heure= sc.next();
				dateDebutLoc = date +" "+heure;
				//on met � jour la reservation avec la nouvelle date et heure de r�servation
				String queryModifdateDebutLoc = "UPDATE RESERVATION SET dateDebutLoc=TO_DATE('"+dateDebutLoc+":00', 'dd/mm/yyyy hh24:mi:ss') WHERE idReserv="+idReserv;
				stmt.executeUpdate(queryModifdateDebutLoc);
				conn.commit();
				System.out.println("La reservation n "+ idReserv+" a bien ete modifiee");
			}
	}
}
